2  Retrieve Data from 2 or More Tables

2.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

  • om
  • ex

2.2 Join Logic

Illustration of common join types:

  • inner join
  • left join
  • right join
  • outer join
  • union (“stacking”)
  • full (outer) join

Note: full join is not available for all SQL dialects. It is a combination of left join + union + right join

SQL Join Types Source

Some other join types:

  • (Left) Semi-Join: returns only rows in the left table that have matching rows in the right table
  • (Left) Anti-Join: returns rows in the left table that have no matching rows in the right table

Note:

  • Semi-join and Anti-join don’t bring any additional fields from other tables.
  • Semi-join and Anti-join are not covered in details here, because MySQL doesn’t have native implementation for these join types.

2.3 Inner Join

tables overview

SELECT *
FROM invoices
ORDER BY invoice_number
LIMIT 10
10 records
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
39 110 0-2058 2022-05-28 37966.19 37966.19 0.00 3 2022-06-27 2022-06-30
106 110 0-2060 2022-07-24 23517.58 21221.63 2295.95 3 2022-08-23 2022-08-27
112 110 0-2436 2022-07-31 10976.06 0.00 0.00 3 2022-08-30 NA
30 123 1-200-5164 2022-05-20 63.40 63.40 0.00 3 2022-06-19 2022-06-24
48 123 1-202-2978 2022-06-03 33.00 33.00 0.00 3 2022-07-03 2022-07-05
21 119 10843 2022-05-11 4901.26 4901.26 0.00 2 2022-05-31 2022-05-29
74 102 109596 2022-06-24 41.80 41.80 0.00 4 2022-08-03 2022-08-04
53 95 111-92R-10092 2022-06-09 46.21 46.21 0.00 2 2022-06-29 2022-07-02
83 95 111-92R-10093 2022-07-06 39.77 39.77 0.00 2 2022-07-26 2022-07-22
44 95 111-92R-10094 2022-06-01 19.67 19.67 0.00 2 2022-06-21 2022-06-24 
SELECT *
FROM vendors 
ORDER BY vendor_name
LIMIT 10
10 records
vendor_id vendor_name vendor_address1 vendor_address2 vendor_city vendor_state vendor_zip_code vendor_phone vendor_contact_last_name vendor_contact_first_name default_terms_id default_account_number
94 Abbey Office Furnishings 4150 W Shaw Ave NA Fresno CA 93722 (559) 555-8300 Francis Kyra 2 150
61 American Booksellers Assoc 828 S Broadway NA Tarrytown NY 10591 (800) 555-0037 Angelica Nashalie 3 574
98 American Express Box 0001 NA Los Angeles CA 90096 (800) 555-3344 Story Kirsten 2 160
15 ASC Signs 1528 N Sierra Vista NA Fresno CA 93703 NA Darien Elisabeth 1 546
66 Ascom Hasler Mailing Systems PO Box 895 NA Shelton CT 06484 NA Lewis Darnell 3 532
93 AT&T PO Box 78225 NA Phoenix AZ 85062 NA Wesley Alisha 3 522
52 Aztek Label Accounts Payable 1150 N Tustin Ave Anaheim CA 92807 (714) 555-9000 Griffin Brian 3 551
47 Baker & Taylor Books Five Lakepointe Plaza, Ste 500 2709 Water Ridge Parkway Charlotte NC 28217 (704) 555-3500 Bernardo Brittnee 3 572
99 Bertelsmann Industry Svcs. Inc 28210 N Avenue Stanford NA Valencia CA 91355 (805) 555-0584 Potter Lance 3 400
8 BFI Industries PO Box 9369 NA Fresno CA 93792 (559) 555-1551 Kaleigh Erick 3 521

Explicit (INNER JOIN)

SELECT invoice_number, vendor_name
FROM vendors INNER JOIN invoices
    ON vendors.vendor_id = invoices.vendor_id
ORDER BY invoice_number
LIMIT 10
10 records
invoice_number vendor_name
0-2058 Malloy Lithographing Inc
0-2060 Malloy Lithographing Inc
0-2436 Malloy Lithographing Inc
1-200-5164 Federal Express Corporation
1-202-2978 Federal Express Corporation
10843 Yesmed, Inc
109596 Coffee Break Service
111-92R-10092 Pacific Bell
111-92R-10093 Pacific Bell
111-92R-10094 Pacific Bell

Compare to the above:

  • table aliases now used
  • just JOIN, instead of full INNER JOIN, now used
SELECT invoice_number, vendor_name, invoice_due_date,
    invoice_total - payment_total - credit_total
    AS balance_due
FROM vendors v JOIN invoices i
    ON v.vendor_id = i.vendor_id
WHERE invoice_total - payment_total - credit_total > 0
ORDER BY invoice_due_date DESC
11 records
invoice_number vendor_name invoice_due_date balance_due
547480102 Blue Cross 2022-08-31 224.00
0-2436 Malloy Lithographing Inc 2022-08-30 10976.06
9982771 Ford Motor Credit Company 2022-08-23 503.20
P-0608 Malloy Lithographing Inc 2022-08-22 19351.18
263253270 Federal Express Corporation 2022-08-21 67.92
263253273 Federal Express Corporation 2022-08-21 30.75
263253268 Federal Express Corporation 2022-08-20 59.97
963253264 Federal Express Corporation 2022-08-17 52.25
134116 Cardinal Business Media, Inc. 2022-08-17 90.36
31361833 Ingram 2022-08-10 579.42
39104 Data Reproductions Corp 2022-08-09 85.31

Compare to the above:

  • alias now refers to a table from a different database
SELECT vendor_name, customer_last_name,
    customer_first_name, vendor_state AS state,
    vendor_city AS city
FROM vendors v
    JOIN om.customers c
    ON v.vendor_zip_code = c.customer_zip
ORDER BY state, city
Displaying records 1 - 15
vendor_name customer_last_name customer_first_name state city
Wells Fargo Bank Marissa Kyle AZ Phoenix
Aztek Label Irvin Ania CA Anaheim
Zylka Design Neftaly Thalia CA Fresno
Lou Gentile’s Flower Basket Damien Deborah CA Fresno
Costco Holbrooke Rashad CA Fresno
Costco Neftaly Thalia CA Fresno
Shields Design Damien Deborah CA Fresno
Wakefield Co Neftaly Thalia CA Fresno
Wakefield Co Holbrooke Rashad CA Fresno
Gary McKeighan Insurance Holbrooke Rashad CA Fresno
Gary McKeighan Insurance Neftaly Thalia CA Fresno
Digital Dreamworks Holbrooke Rashad CA Fresno
Digital Dreamworks Neftaly Thalia CA Fresno
Gostanian General Building Holbrooke Rashad CA Fresno
Gostanian General Building Neftaly Thalia CA Fresno

Implicit joins (WHERE clause instead of ON clause)

SELECT invoice_number, vendor_name
FROM vendors v, invoices i
WHERE v.vendor_id = i.vendor_id
ORDER BY invoice_number
LIMIT 10
10 records
invoice_number vendor_name
0-2058 Malloy Lithographing Inc
0-2060 Malloy Lithographing Inc
0-2436 Malloy Lithographing Inc
1-200-5164 Federal Express Corporation
1-202-2978 Federal Express Corporation
10843 Yesmed, Inc
109596 Coffee Break Service
111-92R-10092 Pacific Bell
111-92R-10093 Pacific Bell
111-92R-10094 Pacific Bell

Implicit joins with (USING keyword), results identical as the above.

SELECT invoice_number, vendor_name
FROM vendors 
    JOIN invoices USING (vendor_id)
ORDER BY invoice_number
Displaying records 1 - 15
invoice_number vendor_name
0-2058 Malloy Lithographing Inc
0-2060 Malloy Lithographing Inc
0-2436 Malloy Lithographing Inc
1-200-5164 Federal Express Corporation
1-202-2978 Federal Express Corporation
10843 Yesmed, Inc
109596 Coffee Break Service
111-92R-10092 Pacific Bell
111-92R-10093 Pacific Bell
111-92R-10094 Pacific Bell
111-92R-10095 Pacific Bell
111-92R-10096 Pacific Bell
111-92R-10097 Pacific Bell
111897 Suburban Propane
121897 Gostanian General Building

Implicit joins with (USING keyword), results identical as the above.

  • use inner join
  • use columns of same names from the tables
  • MySQL, yes, but not for all SQL dialects
SELECT invoice_number, vendor_name
FROM vendors 
    NATURAL JOIN invoices
ORDER BY invoice_number
Displaying records 1 - 15
invoice_number vendor_name
0-2058 Malloy Lithographing Inc
0-2060 Malloy Lithographing Inc
0-2436 Malloy Lithographing Inc
1-200-5164 Federal Express Corporation
1-202-2978 Federal Express Corporation
10843 Yesmed, Inc
109596 Coffee Break Service
111-92R-10092 Pacific Bell
111-92R-10093 Pacific Bell
111-92R-10094 Pacific Bell
111-92R-10095 Pacific Bell
111-92R-10096 Pacific Bell
111-92R-10097 Pacific Bell
111897 Suburban Propane
121897 Gostanian General Building

Join on multiple columns (“combo join”, join based on combination of columns/keys)

table customers overview:

SELECT *
FROM om.customers
Displaying records 1 - 15
customer_id customer_first_name customer_last_name customer_address customer_city customer_state customer_zip customer_phone customer_fax
1 Korah Blanca 1555 W Lane Ave Columbus OH 43221 6145554435 6145553928
2 Yash Randall 11 E Rancho Madera Rd Madison WI 53707 2095551205 2095552262
3 Johnathon Millerton 60 Madison Ave New York NY 10010 2125554800 NULL
4 Mikayla Damion 2021 K Street Nw Washington DC 20006 2025555561 NULL
5 Kendall Mayte 4775 E Miami River Rd Cleves OH 45002 5135553043 NULL
6 Kaitlin Hostlery 3250 Spring Grove Ave Cincinnati OH 45225 8005551957 8005552826
7 Derek Chaddick 9022 E Merchant Wy Fairfield IA 52556 5155556130 NULL
8 Deborah Damien 415 E Olive Ave Fresno CA 93728 5595558060 NULL
9 Karina Lacy 882 W Easton Wy Los Angeles CA 90084 8005557000 NULL
10 Kurt Nickalus 28210 N Avenue Stanford Valencia CA 91355 8055550584 055556689
11 Kelsey Eulalia 7833 N Ridge Rd Sacramento CA 95887 2095557500 2095551302
12 Anders Rohansen 12345 E 67th Ave NW Takoma Park MD 24512 3385556772 NULL
13 Thalia Neftaly 2508 W Shaw Ave Fresno CA 93711 5595556245 NULL
14 Gonzalo Keeton 12 Daniel Road Fairfield NJ 07004 2015559742 NULL
15 Ania Irvin 1099 N Farcourt St Orange CA 92807 7145559000 NULL 
SELECT *
FROM ex.employees
9 records
employee_id last_name first_name department_number manager_id
1 Smith Cindy 2 NA
2 Jones Elmer 4 1
3 Simonian Ralph 2 2
4 Hernandez Olivia 1 9
5 Aaronsen Robert 2 4
6 Watson Denise 6 8
7 Hardy Thomas 5 2
8 O’Leary Rhea 4 9
9 Locario Paulo 6 1
  • “Are there any customers and employees with the same first and last name?”
SELECT customer_first_name, customer_last_name
FROM om.customers c INNER JOIN ex.employees e 
    ON c.customer_first_name = e.first_name 
   AND c.customer_last_name = e.last_name
0 records
customer_first_name customer_last_name

A Somewhat Special Example - Self Join

To make self-join work:

  • must use alias to avoid recycling same table reference

  • “In those cities where more than 1 vendors reside, what are the OTHER vendors that in the same city and state?”

Explore the vendor table:

SELECT *
FROM vendors v1
ORDER BY v1.vendor_id, v1.vendor_state, v1.vendor_city
Displaying records 1 - 15
vendor_id vendor_name vendor_address1 vendor_address2 vendor_city vendor_state vendor_zip_code vendor_phone vendor_contact_last_name vendor_contact_first_name default_terms_id default_account_number
1 US Postal Service Attn: Supt. Window Services PO Box 7005 Madison WI 53707 (800) 555-1205 Alberto Francesco 1 552
2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540
3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572
5 Newbrige Book Clubs 3000 Cindel Drive NA Washington NJ 07882 (800) 555-9980 Marks Michelle 4 394
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572
7 Towne Advertiser’s Mailing Svcs Kevin Minder 3441 W Macarthur Blvd Santa Ana CA 92704 NA Maegen Ted 3 540
8 BFI Industries PO Box 9369 NA Fresno CA 93792 (559) 555-1551 Kaleigh Erick 3 521
9 Pacific Gas & Electric Box 52001 NA San Francisco CA 94152 (800) 555-6081 Anthoni Kaitlyn 3 521
10 Robbins Mobile Lock And Key 4669 N Fresno NA Fresno CA 93726 (559) 555-9375 Leigh Bill 2 523
11 Bill Marvin Electric Inc 4583 E Home NA Fresno CA 93703 (559) 555-5106 Hostlery Kaitlin 2 523
12 City Of Fresno PO Box 2069 NA Fresno CA 93718 (559) 555-9999 Mayte Kendall 3 574
13 Golden Eagle Insurance Co PO Box 85826 NA San Diego CA 92186 NA Blanca Korah 3 590
14 Expedata Inc 4420 N. First Street, Suite 108 NA Fresno CA 93726 (559) 555-9586 Quintin Marvin 3 589
15 ASC Signs 1528 N Sierra Vista NA Fresno CA 93703 NA Darien Elisabeth 1 546

Step 1: simple self-joining based on city and state of vendor

SELECT *
FROM vendors v1 INNER JOIN vendors v2
    ON v1.vendor_city = v2.vendor_city AND
       v1.vendor_state = v2.vendor_state
ORDER BY v1.vendor_id, v1.vendor_state, v1.vendor_city
Displaying records 1 - 15
vendor_id vendor_name vendor_address1 vendor_address2 vendor_city vendor_state vendor_zip_code vendor_phone vendor_contact_last_name vendor_contact_first_name default_terms_id default_account_number vendor_id vendor_name vendor_address1 vendor_address2 vendor_city vendor_state vendor_zip_code vendor_phone vendor_contact_last_name vendor_contact_first_name default_terms_id default_account_number
1 US Postal Service Attn: Supt. Window Services PO Box 7005 Madison WI 53707 (800) 555-1205 Alberto Francesco 1 552 1 US Postal Service Attn: Supt. Window Services PO Box 7005 Madison WI 53707 (800) 555-1205 Alberto Francesco 1 552
2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540 2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540
2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540 3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403
2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540 82 Reiter’s Scientific & Pro Books 2021 K Street Nw NA Washington DC 20006 (202) 555-5561 Rodolfo Carlee 2 572
3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403 82 Reiter’s Scientific & Pro Books 2021 K Street Nw NA Washington DC 20006 (202) 555-5561 Rodolfo Carlee 2 572
3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403 2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540
3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403 3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 42 Opamp Technical Books 1033 N Sycamore Ave. NA Los Angeles CA 90038 (213) 555-4322 Paris Gideon 3 572
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 106 Ford Motor Credit Company Dept 0419 NA Los Angeles CA 90084 (800) 555-7000 Snyder Karen 3 582
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 112 Office Depot File No 81901 NA Los Angeles CA 90074 (800) 555-1711 Pinsippi Val 3 570
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 98 American Express Box 0001 NA Los Angeles CA 90096 (800) 555-3344 Story Kirsten 2 160
5 Newbrige Book Clubs 3000 Cindel Drive NA Washington NJ 07882 (800) 555-9980 Marks Michelle 4 394 5 Newbrige Book Clubs 3000 Cindel Drive NA Washington NJ 07882 (800) 555-9980 Marks Michelle 4 394
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572 67 Bill Jones Secretary Of State PO Box 944230 Sacramento CA 94244 NA Deasia Tristin 3 589
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572 77 State Board Of Equalization PO Box 942808 NA Sacramento CA 94208 (916) 555-4911 Dean Julissa 1 631

Unequal join to remove the vendor itself, so that the rest of the vendors are left in the result:

SELECT *
FROM vendors v1 INNER JOIN vendors v2
    ON v1.vendor_city = v2.vendor_city AND
       v1.vendor_state = v2.vendor_state AND
       v1.vendor_name <> v2.vendor_name
ORDER BY v1.vendor_id, v1.vendor_state, v1.vendor_city
Displaying records 1 - 15
vendor_id vendor_name vendor_address1 vendor_address2 vendor_city vendor_state vendor_zip_code vendor_phone vendor_contact_last_name vendor_contact_first_name default_terms_id default_account_number vendor_id vendor_name vendor_address1 vendor_address2 vendor_city vendor_state vendor_zip_code vendor_phone vendor_contact_last_name vendor_contact_first_name default_terms_id default_account_number
2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540 82 Reiter’s Scientific & Pro Books 2021 K Street Nw NA Washington DC 20006 (202) 555-5561 Rodolfo Carlee 2 572
2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540 3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403
3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403 2 National Information Data Ctr PO Box 96621 NA Washington DC 20120 (301) 555-8950 Irvin Ania 3 540
3 Register of Copyrights Library Of Congress NA Washington DC 20559 NA Liana Lukas 3 403 82 Reiter’s Scientific & Pro Books 2021 K Street Nw NA Washington DC 20006 (202) 555-5561 Rodolfo Carlee 2 572
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 42 Opamp Technical Books 1033 N Sycamore Ave. NA Los Angeles CA 90038 (213) 555-4322 Paris Gideon 3 572
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 98 American Express Box 0001 NA Los Angeles CA 90096 (800) 555-3344 Story Kirsten 2 160
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 112 Office Depot File No 81901 NA Los Angeles CA 90074 (800) 555-1711 Pinsippi Val 3 570
4 Jobtrak 1990 Westwood Blvd Ste 260 NA Los Angeles CA 90025 (800) 555-8725 Quinn Kenzie 3 572 106 Ford Motor Credit Company Dept 0419 NA Los Angeles CA 90084 (800) 555-7000 Snyder Karen 3 582
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572 95 Pacific Bell NA NA Sacramento CA 95887 (209) 555-7500 Nickalus Kurt 2 522
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572 116 State of California Employment Development Dept PO Box 826276 Sacramento CA 94230 (209) 555-5132 Articunia Mercedez 1 631
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572 107 Franchise Tax Board PO Box 942857 NA Sacramento CA 94257 NA Prado Anita 4 507
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572 87 DMV Renewal PO Box 942894 NA Sacramento CA 94294 NA Josey Lorena 4 568
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572 77 State Board Of Equalization PO Box 942808 NA Sacramento CA 94208 (916) 555-4911 Dean Julissa 1 631
6 California Chamber Of Commerce 3255 Ramos Cir NA Sacramento CA 95827 (916) 555-6670 Mauro Anton 3 572 67 Bill Jones Secretary Of State PO Box 944230 Sacramento CA 94244 NA Deasia Tristin 3 589
8 BFI Industries PO Box 9369 NA Fresno CA 93792 (559) 555-1551 Kaleigh Erick 3 521 63 Lou Gentile’s Flower Basket 722 E Olive Ave NA Fresno CA 93728 (559) 555-6643 Anum Trisha 1 570

Take distinct values of what’s left in the “left table”. These are the other vendors in the same cities and states.

SELECT DISTINCT v1.vendor_name, v1.vendor_city, 
    v1.vendor_state
FROM vendors v1 INNER JOIN vendors v2
    ON v1.vendor_city = v2.vendor_city AND
       v1.vendor_state = v2.vendor_state AND
       v1.vendor_name <> v2.vendor_name
ORDER BY v1.vendor_state, v1.vendor_city
Displaying records 1 - 15
vendor_name vendor_city vendor_state
Computer Library Phoenix AZ
Wells Fargo Bank Phoenix AZ
AT&T Phoenix AZ
Aztek Label Anaheim CA
Blue Shield of California Anaheim CA
Shields Design Fresno CA
Graylift Fresno CA
ASC Signs Fresno CA
Internal Revenue Service Fresno CA
Fresno Photoengraving Company Fresno CA
Costco Fresno CA
Crown Printing Fresno CA
Yale Industrial Trucks-Fresno Fresno CA
Zylka Design Fresno CA
Dataforms/West Fresno CA

2.4 Outer Join

This includes:

  • left join
  • right join
  • full outer join

tables overview

SELECT *
FROM ex.departments
LIMIT 10
5 records
department_number department_name
1 Accounting
2 Payroll
3 Operations
4 Personnel
5 Maintenance 
SELECT *
FROM ex.employees 
LIMIT 10
9 records
employee_id last_name first_name department_number manager_id
1 Smith Cindy 2 NA
2 Jones Elmer 4 1
3 Simonian Ralph 2 2
4 Hernandez Olivia 1 9
5 Aaronsen Robert 2 4
6 Watson Denise 6 8
7 Hardy Thomas 5 2
8 O’Leary Rhea 4 9
9 Locario Paulo 6 1 
SELECT *
FROM ex.projects 
LIMIT 10
8 records
project_number employee_id
P1011 8
P1011 4
P1012 3
P1012 1
P1012 5
P1013 6
P1013 9
P1014 10

Left Join

SELECT department_name, last_name, project_number
FROM ex.departments d
    LEFT JOIN ex.employees e
        ON d.department_number = e.department_number
    LEFT JOIN ex.projects p
        ON e.employee_id = p.employee_id
ORDER BY department_name, last_name
8 records
department_name last_name project_number
Accounting Hernandez P1011
Maintenance Hardy NA
Operations NA NA
Payroll Aaronsen P1012
Payroll Simonian P1012
Payroll Smith P1012
Personnel Jones NA
Personnel O’Leary P1011

Right Join

SELECT department_name, e.department_number, last_name
FROM ex.departments d 
    RIGHT JOIN ex.employees e
    ON d.department_number = e.department_number
ORDER BY department_name
9 records
department_name department_number last_name
NA 6 Watson
NA 6 Locario
Accounting 1 Hernandez
Maintenance 5 Hardy
Payroll 2 Smith
Payroll 2 Simonian
Payroll 2 Aaronsen
Personnel 4 Jones
Personnel 4 O’Leary

combine different join types:

SELECT department_name, last_name, project_number
FROM ex.departments d
    INNER JOIN ex.employees e
        ON d.department_number = e.department_number
    LEFT JOIN ex.projects p
        ON e.employee_id = p.employee_id
ORDER BY department_name, last_name
7 records
department_name last_name project_number
Accounting Hernandez P1011
Maintenance Hardy NA
Payroll Aaronsen P1012
Payroll Simonian P1012
Payroll Smith P1012
Personnel Jones NA
Personnel O’Leary P1011

Cross Join

It creates all combinations of rows from different tables.

tables overview:

SELECT *
FROM ex.departments
LIMIT 10
5 records
department_number department_name
1 Accounting
2 Payroll
3 Operations
4 Personnel
5 Maintenance 
SELECT *
FROM ex.employees 
LIMIT 10
9 records
employee_id last_name first_name department_number manager_id
1 Smith Cindy 2 NA
2 Jones Elmer 4 1
3 Simonian Ralph 2 2
4 Hernandez Olivia 1 9
5 Aaronsen Robert 2 4
6 Watson Denise 6 8
7 Hardy Thomas 5 2
8 O’Leary Rhea 4 9
9 Locario Paulo 6 1

cross join:

SELECT departments.department_number, department_name,
    employee_id, last_name
FROM ex.departments CROSS JOIN ex.employees
ORDER BY departments.department_number
Displaying records 1 - 15
department_number department_name employee_id last_name
1 Accounting 9 Locario
1 Accounting 1 Smith
1 Accounting 8 O’Leary
1 Accounting 4 Hernandez
1 Accounting 2 Jones
1 Accounting 7 Hardy
1 Accounting 5 Aaronsen
1 Accounting 3 Simonian
1 Accounting 6 Watson
2 Payroll 9 Locario
2 Payroll 4 Hernandez
2 Payroll 1 Smith
2 Payroll 8 O’Leary
2 Payroll 2 Jones
2 Payroll 7 Hardy

Union

This is essentially stacking different intermediate table results one on top of another:

  • Each result set must return the same number of columns.
  • The corresponding columns in each result set must have compatible data types.
  • The column names in the final result set are taken from the first SELECT clause.
SELECT 'Active' AS source, 
  invoice_number, 
  invoice_date, 
  invoice_total
FROM ex.active_invoices
WHERE invoice_date >= '2022-06-01'

UNION

SELECT 'Paid' AS source, 
  invoice_number, 
  invoice_date, 
  invoice_total
FROM ex.paid_invoices
WHERE invoice_date >= '2022-06-01'
ORDER BY invoice_total DESC
Displaying records 1 - 15
source invoice_number invoice_date invoice_total
Active 40318 2022-07-18 21842.00
Paid P02-3772 2022-06-03 7125.34
Paid 10843 2022-06-04 4901.26
Paid 77290 2022-06-04 1750.00
Paid RTR-72-3662-X 2022-06-04 1600.00
Paid 75C-90227 2022-06-06 1367.50
Paid P02-88D77S7 2022-06-06 856.92
Active I77271-O01 2022-06-05 662.00
Active 9982771 2022-06-03 503.20
Paid 121897 2022-06-01 450.00
Paid CBM9920-M-T77109 2022-06-07 290.00
Paid 133560 2022-06-01 175.00
Active 134116 2022-06-01 90.36
Active 39104 2022-06-20 85.31
Active 111-92R-10092 2022-06-04 46.21

Full Outer Join

A combination of left outer join and right outer join.

There is no keywords in MySQL to do full outer join directly. Other SQL dialects might have (e.g. T-SQL has FULL (OUTER) JOIN)

in MySQL it is literally a UNION of LEFT JOIN set and RIGHT JOIN set

SELECT department_name AS dept_name,
        d.department_number AS d_dept_no,
        e.department_number AS e_dept_no, last_name
FROM ex.departments d 
     LEFT JOIN ex.employees e 
     ON d.department_number = e.department_number

UNION

SELECT department_name AS dept_name,
       d.department_number AS d_dept_no,
       e.department_number AS e_dept_no, last_name
FROM ex.departments d 
    RIGHT JOIN ex.employees e 
    ON d.department_number = e.department_number

ORDER BY dept_name
10 records
dept_name d_dept_no e_dept_no last_name
NA NA 6 Watson
NA NA 6 Locario
Accounting 1 1 Hernandez
Maintenance 5 5 Hardy
Operations 3 NA NA
Payroll 2 2 Aaronsen
Payroll 2 2 Simonian
Payroll 2 2 Smith
Personnel 4 4 O’Leary
Personnel 4 4 Jones